﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[esf_files].[P_GetFileReference]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [esf_files].[P_GetFileReference]
GO


CREATE PROCEDURE esf_files.P_GetFileReference
	@pID INT = NULL
	,@pURL VARCHAR (MAX) = NULL
	,@pMonitoredDirectoryPath VARCHAR (MAX) = NULL
	,@pMonitoredDirectoryID INT = NULL
	,@pParentFileReferenceID INT = NULL
AS

	SELECT
		FR.*
	FROM
		esf_files.FileReference FR
	WHERE
		(@pID IS NULL OR FR.ID = @pID)
		AND (@pURL IS NULL OR FR.URL = @pURL)
		AND (@pMonitoredDirectoryID IS NULL OR FR.MonitoredDirectoryID = @pMonitoredDirectoryID)
		AND (@pMonitoredDirectoryPath IS NULL OR FR.URL LIKE @pMonitoredDirectoryPath)
		AND (@pParentFileReferenceID IS NULL OR FR.ParentFileReferenceID = @pParentFileReferenceID)